﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

namespace template_dll
{
    public partial class Form3 : Form
    {
        public Form3()
        {
            InitializeComponent();
        }

        private DataTable dt_pharmsup = new DataTable();
        private DataTable dt_email = new DataTable();
        public SqlConnection conn;

        private void textBox1_TextChanged(object sender, EventArgs e)
        {
            HideShowRows();
        }

        private void textBox1_Enter(object sender, EventArgs e)
        {
            HideShowRows();
        }

        private void HideShowRows()
        {
            CurrencyManager cm = (CurrencyManager)dataGridView1.BindingContext[dataGridView1.DataSource];
            cm.SuspendBinding();
            foreach (DataGridViewRow r in dataGridView1.Rows)
            {
                r.Visible = true;
                if (!Regex.Match(Convert.ToString(dataGridView1[dataGridView1.SortedColumn.Name, r.Index].Value), textBox1.Text, RegexOptions.IgnoreCase).Success)
                    r.Visible = false;
            }
            cm.ResumeBinding();
        }

        private void Form3_Load(object sender, EventArgs e)
        {
            PharmSupManagerSel();
            SqlCommand comm = conn.CreateCommand();
            comm.CommandText = "pas_PharmEmailSel";
            comm.CommandType = CommandType.StoredProcedure;
            if(conn.State == ConnectionState.Closed) conn.Open();
            dt_email.Load(comm.ExecuteReader());
            dataGridView1.DataSource = dt_email.DefaultView;
            dataGridView1.Sort(dataGridView1.Columns[1], ListSortDirection.Ascending);
            HideShowRows();
        }

        private void PharmSupManagerSel()
        {
            try
            {
                dt_pharmsup = null;
                dt_pharmsup = new DataTable();
                SqlCommand comm = conn.CreateCommand();
                comm.CommandType = CommandType.StoredProcedure;
                comm.CommandText = "pas_PharmSupManagerSel";
                if (conn.State == ConnectionState.Closed) conn.Open();
                dt_pharmsup.Load(comm.ExecuteReader());
                if (dt_pharmsup.Rows.Count > 0)
                {
                    foreach (DataRow row in dt_pharmsup.Rows)
                    {
                        comboBox1.Items.Add(row[1].ToString());
                    }
                    comboBox1.SelectedIndex = 0;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                if (comboBox1.Text != "" && textBox2.Text != "")
                {
                    if (MessageBox.Show("Аптека " + comboBox1.Text + "\nадрес " + textBox2.Text, "Добавить новую запись?", MessageBoxButtons.YesNo) == DialogResult.Yes)
                    {
                        dataGridView1.DataSource = null;
                        dt_email = null;
                        dt_email = new DataTable();
                        SqlCommand comm = conn.CreateCommand();
                        comm.CommandType = CommandType.StoredProcedure;
                        comm.CommandText = "pas_PharmEmailInsDel";
                        comm.Parameters.AddWithValue("@del", 0);
                        comm.Parameters.AddWithValue("@email", textBox2.Text);
                        foreach (DataRow row in dt_pharmsup.Rows)
                        {
                            if (comboBox1.Text == row[1].ToString())
                                comm.Parameters.AddWithValue("@pharmcode", row[0]);
                        }
                        if (conn.State == ConnectionState.Closed) conn.Open();
                        dt_email.Load(comm.ExecuteReader());
                        dataGridView1.DataSource = dt_email.DefaultView;
                        dataGridView1.Sort(dataGridView1.Columns[1], ListSortDirection.Ascending);
                        HideShowRows();
                    }
                }
                else MessageBox.Show("Не выбрана аптека или не введена почта");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            try
            {
                int rowind = dataGridView1.CurrentRow.Index;
                if (MessageBox.Show("Аптека " + dataGridView1[1, rowind].Value.ToString() + "\nадрес " + dataGridView1[2, rowind].Value.ToString()
                    , "Удалить запись?", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    SqlCommand comm = conn.CreateCommand();
                    comm.CommandType = CommandType.StoredProcedure;
                    comm.CommandText = "pas_PharmEmailInsDel";
                    comm.Parameters.AddWithValue("@del", 1);
                    comm.Parameters.AddWithValue("@email", dataGridView1[2,rowind].Value);
                    comm.Parameters.AddWithValue("@pharmcode", dataGridView1[0, rowind].Value);
                    if (conn.State == ConnectionState.Closed) conn.Open();
                    dataGridView1.DataSource = null;
                    dt_email = null;
                    dt_email = new DataTable();
                    dt_email.Load(comm.ExecuteReader());
                    dataGridView1.DataSource = dt_email.DefaultView;
                    dataGridView1.Sort(dataGridView1.Columns[1], ListSortDirection.Ascending);
                    HideShowRows();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
